const Service = require('egg').Service;
const moment = require('moment');
const Sequelize = require('sequelize');

class ArticleService extends Service {
  // 随机生成唯一文章id
  async getRandomArticleId() {
    const { ctx, app } = this;
    try {
      // 文章id生成规则：时间戳_随机十位数
      let articleId;
      let random = '';
      const timeStamp = new Date().getTime().toString();
      for (let i = 1; i <= 10; i++) {
        const num = Math.floor(Math.random() * 10);
        random += num;
      }
      articleId = `${timeStamp}_${random}`;
      return {
        success: true,
        msg: '查询成功',
        articleId,
      };
    } catch (e) {
      return {
        success: false,
        msg: '查询失败',
        code: e.toString(),
      };
    }
  }

  // 根据条件获取文章列表数据
  async getArticleList() {
    const { ctx, app } = this;
    const { type, page, keywords, userId, edit } = ctx.request.query;
    let sql;
    let where = ' where 1=1'
    if (edit && userId != '1')  where += ` AND user_id = ${userId}`
     
    else if (userId && userId != '1') {
      where += ` AND visible = 1 OR (visible = 0 AND user_id = ${userId}))` 
    } else where += ' AND visible = 1'

    if (type) where += ` AND type = '${type}'`

    if (keywords) where += ` AND title LIKE '%${keywords}%'`

    sql = 'SELECT id,user_id,author,title,type,introduce,hot,favour,visible,create_date,update_date FROM `article_list` ' + where + ' ORDER BY `update_date` DESC ' + (page ? ' LIMIT ' + `${(page - 1) * 10}` + ', 10' : '');
    try {
      const res = await app.model.query(sql)
      return {
        success: true,
        msg: '查询成功',
        results: res[0]
      };
    } catch (e) {
      return {
        success: false,
        msg: '查询失败',
        code: e.toString(),
      };
    }
  }

  // 文章点赞
  async addFavour() {
    const { ctx, app } = this;
    // 创建mysql事务
    let transaction = await app.model.transaction()
    let { id, favour } = ctx.request.body;
    favour++;
    let res = null
    try {
      await app.model.ArticleList.update({ favour }, {
        where: {
          id
        }
      }, {transaction});
      // 提交事务
      await transaction.commit();
      res = {
        success: true,
        msg: '操作成功',
        favour
      };
    } catch (error) {
      await transaction.rollback();
      res = {
        success: false,
        msg: '操作失败',
        code: error.toString(),
      };
    }
    return res
  }
  //获取评论列表
  async getComments(articleId, userId, keyword, state, page, size) {
    let { ctx, app } = this
    let res = null
    let sql = null
    if (userId) {
      sql = `select ac.id,ac.article_id,ac.content,ac.user_id,ac.username,ac.create_time,ac.state, 
      ar.id as reply_id, ar.content as reply_content, ac.user_id as reply_userId,
      ac.username as reply_username,ar.create_time as reply_time,
      al.title
      from article_comments ac 
      left join article_replys ar on ac.id = ar.comment_id
      left join article_list al on al.id = ac.article_id
      where al.user_id = ${userId} ${keyword ? ` AND LOCATE('${keyword}', ac.content)>0` : ''}
      ${state ? ' AND ac.state = '+state+'' : ''}
      order by ac.create_time, ar.create_time desc` + (page ? ' LIMIT ' + `${(page - 1) * size}, ${size}` : '')
    } else {
      let where = 'where 1=1'
      if (articleId) where += ` AND ac.article_id = '${articleId}'`
      if (keyword) where += ` AND LOCATE('${keyword}', ac.content)>0`
      if (state) where += ` AND ac.state = '${state}'`
      sql = `select ac.id,ac.article_id,ac.content,ac.user_id,ac.username,ac.create_time,ac.state, 
      ar.id as reply_id, ar.content as reply_content, ac.user_id as reply_userId,
      ac.username as reply_username,ar.create_time as reply_time
      from article_comments ac 
      left join article_replys ar on ac.id = ar.comment_id
      ${where}
      order by ac.create_time, ar.create_time desc`+ (page ? ' LIMIT ' + `${(page - 1) * size}, ${size}` : '')
    }
     
    try {
      let result = await app.model.query(sql)
      let comments = []
      let data = result[0]
      if (data.length) {
        let map = new Map()
        data.forEach( item => {
            let idx = map.get(item['id'])
            if (idx || idx === 0) {
              comments[idx].replies.push({
                content: item['reply_content'],
                createdAt: item['reply_time'],
                id: item['reply_id'],
                userId: item['reply_userId'],
                username: item['reply_username'],
              })
            } else {
              map.set(item['id'], comments.length)
              comments.push({
                content: item['content'],
                title: item['title'],
                createdAt: item['create_time'],
                id: item['id'],
                userId: item['user_id'],
                username: item['username'],
                articleId: item['article_id'],
                state: item['state'],
                replies: item['reply_id'] ? [{
                  content: item['reply_content'],
                  createdAt: item['reply_time'],
                  id: item['reply_id'],
                  userId: item['reply_userId'],
                  username: item['reply_username'],
                }] : [],
              })
            }
        })
      }
      res = {
        success: true,
        comments
      }
    } catch (error) {
      res = {
        success: false,
        comments: [],
        code: error.toString()
      }
    }
    return res
  }
  // 提交评论
  async comment() {
    let { ctx, app } = this
    // 创建mysql事务
    let transaction = await app.model.transaction()
    const { articleId, content, userId, username } = ctx.request.body
    let res = null
    try {
      await app.model.ArticleComments.create({article_id: articleId, content, user_id: userId, username}, {transaction})
      res = await ctx.service.article.getComments(articleId)
      await transaction.commit()
    } catch (e) {
      await transaction.rollback()
      res = {
        success: false,
        code: e.toString()
      }
    }
    return res
  }
  // 提交回复
  async reply() {
    let { ctx, app } = this
    // 创建mysql事务
    let transaction = await app.model.transaction()
    const { articleId, commentId, content, userId, username, updateState, state } = ctx.request.body
    let res = null

    try {
      if (updateState) {
        await ctx.service.article.updateCommentState(commentId, state)
      }
      await app.model.ArticleReplys.create({ comment_id: commentId, content, user_id: userId, username }, {transaction})
      res = await ctx.service.article.getComments(articleId)
      await transaction.commit()
    } catch (e) {
      await transaction.rollback()
      res = {
        success: false,
        code: e.toString()
      }
    }
    return res
  }
  // 删除评论
  async deleteComment() {
    let { app, ctx} = this
    let { userId, commentId, keyword, pageNum, pageSize } = ctx.request.query
    let res = null
    let sql = `delete from article_comments ac left join article_replys ar
      on ac.id = ar.comment_id where ac.id = ${commentId}`
    try {
      await app.model.query(sql)
      let queryRes = await ctx.service.article.getComments('', userId, keyword, state, pageNum, pageSize)
      res = {
        success: true,
        comments: queryRes.comments,
        msg: '删除成功'
      }
    } catch (e) {
      res = {
        success: true,
        code: e.toString()
      }
    }
    return res
  }
  // 修改评论状态
  async updateCommentState(commentId, state) {
    const {app, ctx} = this
    const transaction = await app.model.transaction()
    let res = null
    try {
      await app.model.ArticleComments.update({state}, {
        where: {
          id: commentId
        }
      }, {transaction})
      await transaction.commit()
      res = {
        success: true
      }
    } catch (e) {
      await transaction.rollback()
      res = {
        success: true,
        code: e.toString()
      }
    }
    return res
  }
  // 获取所有文章类型
  async getArticleType(code, keywords) {
    const { ctx, app } = this;
    const Op = Sequelize.Op
    let where = {}
    if (code) where['code'] = code
    if (keywords) //where += ` AND LOCATE('${keywords}', code)>0 OR LOCATE('${keywords}', name)>0 `
    where[[Op.or]] = [
      { code: {[Op.like]: '%'+keywords+'%' }}, {name: {[Op.like]: '%'+keywords+'%' }}
    ]
    if (!code && !keywords) where = {}
    
    try {
      //const res = await app.mysql.query('SELECT * FROM `article_type` '+where+' ORDER BY `id` LIMIT 0, 10');
      const res = await app.model.ArticleType.findAll({
         where,
         order: [['id', 'DESC']]
      })
      
      for (const [ index, item ] of res.entries()) {
        // const total = await Sequelize.query('SELECT Count(*) FROM article_list WHERE article_list.type = ' + `'${item.name}'`, {
        //   type: Sequelize.QueryTypes.SELECT
        // });
        const { count } = await app.model.ArticleList.findAndCountAll({
          where: {
            type: item.name
          }
        })
        res[index].total = count
      }
      return {
        success: true,
        msg: '查询成功',
        results: res,
      };
    } catch (e) {
      return {
        success: false,
        msg: '查询失败',
        code: e.toString(),
      };
    }
  }

  // 根据文章类型Id删除
  async deleteArticleType() {
    const { ctx, app } = this;
    // 创建mysql事务
    let transaction = await app.model.transaction()
    let { Id } = ctx.request.body
    let res = null
    // 建立事务对象
    try {
      await app.model.ArticleType.destroy({
        where: {
          id: Id
        }
      }, {transaction})
      await transaction.commit()
      res = {
        success: true,
        msg: '操作成功',
      };
    } catch (error) {
      // 回滚事务
      await transaction.rollback()
      res = {
        success: false,
        msg: '操作失败',
        code: error.toString(),
      };
    }
    return res;
  }

  // 编辑文章类型
  async updateArticleType() {
    const { ctx, app } = this
    // 创建mysql事务
    let transaction = await app.model.transaction();
    const { id, code, name, remark } = ctx.request.body;

    let res = null
    try {
      await app.model.ArticleType.update({code, name, remark, update_date: moment().format('YYYY-MM-DD HH:mm:ss')}, {
        where: {
          id
        }
      }, { transaction });
      // 提交事务
      await transaction.commit();
      res = {
        success: true,
        msg: '操作成功',
      };
    } catch (e) {
      // 回滚事务
      await transaction.rollback();
      res = {
        success: false,
        msg: '操作失败',
        code: e.toString(),
      };
    }
    return res;
  }

  // 新增文章类型
  async addArticleType() {
    const { ctx, app } = this;
    // 创建mysql事务
    let transaction = await app.model.transaction();
    const { code, name, remark } = ctx.request.body
    let res = null;
    try {
      await app.model.ArticleType.create({code, name, remark}, {
        transaction
      })
      await transaction.commit()
      res = {
        success: true,
        msg: '新增成功',
      }
    } catch (error) {
      // 回滚事务
      await transaction.rollback();
      res = {
        success: false,
        msg: '操作失败',
        code: error.toString(),
      };
    }
    return res;
  }

  // 根据文章id查文章数据（不包括详情内容）
  async getOtherMsgById(id) {
    const { ctx, app } = this;
    try {
      const res = await app.model.ArticleList.findOne({
        where: {
          id
        }
      })
      return {
        success: true,
        msg: '查询成功',
        results: res,
      };
    } catch (e) {
      return {
        success: false,
        msg: '查询失败',
        code: e.toString(),
      };
    }
  }

  // 根据文章id获取详细内容
  async getArticleDetail(article_id, author, shoulUpdate) {
    const { ctx, app } = this;

    try {
      // 获取当前文章详情内容
      const contentMsg = await ctx.service.file.readArticle(article_id, author);
      // 获取当前文章其他信息
      const { results: otherMsg } = await ctx.service.article.getOtherMsgById(article_id);
      
      // 是否更新浏览量
      if (shoulUpdate === 'true') {
        await ctx.service.article.updateArticleHotById(article_id, otherMsg.hot);
      }

      if (contentMsg.success) {
        return {
          success: true,
          msg: '查询成功',
          results: [{
            author: otherMsg['author'],
            create_date: otherMsg['create_date'],
            favour: otherMsg['favour'],
            hot: otherMsg['hot'],
            introduce: otherMsg['introduce'],
            title: otherMsg['title'],
            type: otherMsg['type'],
            update_date: otherMsg['update_date'],
            content: contentMsg.data,
          }],
        };
      }
      return {
        success: false,
        msg: '查询失败',
        contentMsg,
        code: '数据出现空异常',
      };

    } catch (e) {
      return {
        success: false,
        msg: '查询失败',
        code: e.toString(),
      };
    }

  }

  // 根据文章id更新文章浏览量
  async updateArticleHotById(articleId, oldHot) {
    const numberOldHot = Number(oldHot);
    const { ctx, app } = this;
    let transaction = await app.model.transaction();
    try {
      await app.model.ArticleList.update({hot: numberOldHot + 1}, {
        where: {
          id: articleId
        }
      }, {transaction})
      await transaction.commit();
      return {
        success: true,
        msg: '操作成功',
      };
    } catch (e) {
      await transaction.rollback();
      return {
        success: false,
        msg: '操作失败',
        code: e.toString(),
      };
    }

  }

  // 获取博客的文章总数量和总访问量
  async getArticleTotals(userId) {
    const { ctx, app } = this;
    let where = {}
    if (userId) {
      where['user_id'] = userId
    } else where['visible'] = 1
    try {
      //const Total = await app.mysql.query(`SELECT Count(*), sum(hot) FROM article_list ${where}`);
      const {count, rows } = await app.model.ArticleList.findAndCountAll({
        where
      })
      let total = 0
      rows.forEach(cur => total += Number(cur['hot']))
      return {
        success: true,
        msg: '查询成功',
        totals: {
          hot: total,
          article: count,
        },
      };
    } catch (e) {
      return {
        success: false,
        msg: '查询失败',
        code: e.toString(),
      };
    }

  }

}

module.exports = ArticleService;
